<?php
class ModelSaleStockOut extends Model {

	public function add($data){
		$sql = "INSERT INTO `".DB_PREFIX."stock_out`";

		if(!empty($data['refer_id'])){
			$set[] = "refer_id = '".$data['refer_id']."'";
		}

		if(!empty($data['refer_type_id'])){
			$set[] = "refer_type_id = '".$data['refer_type_id']."'";
		}

		if(!empty($data['warehouse_id'])){
			$set[] = "warehouse_id = '".$data['warehouse_id']."'";
		}

		if(!empty($data['user_id'])){
			$set[] = "user_id = '".$data['user_id']."'";
		}

		if(!empty($data['sale_money'])){
			$set[] = "sale_money = '".$data['sale_money']."'";
		}

        if(!empty($data['pay_sale_money'])){//出库单摊销总价
			$set[] = "pay_sale_money = '".$data['pay_sale_money']."'";
		}

        if(!empty($data['status'])){
			$set[] = "status = '".$data['status']."'";
		}

		if(!empty($data['out_date'])){
			$set[] = "out_date = '".$data['out_date']."'";
		}

		if(!empty($data['date_added'])){
			$set[] = "date_added = '".$data['date_added']."'";
		}else{
			$set[] = "date_added = NOW()";
		}

		if(!empty($data['comment'])){
			$set[] = "comment = '".$data['comment']."'";
		}

		$sql .= ' SET '.implode(',',$set);

		$this->db->query($sql);

		$out_id = $this->db->getLastId();

		return $out_id;
	}

	public function update($data,$out_id){
		$sql = "UPDATE `".DB_PREFIX."stock_out`";

		if(!empty($data['refer_id'])){
			$set[] = "refer_id = '".$data['refer_id']."'";
		}

		if(!empty($data['refer_type_id'])){
			$set[] = "refer_type_id = '".$data['refer_type_id']."'";
		}

		if(!empty($data['warehouse_id'])){
			$set[] = "warehouse_id = '".$data['warehouse_id']."'";
		}

		if(!empty($data['user_id'])){
			$set[] = "user_id = '".$data['user_id']."'";
		}

		if(!empty($data['sale_money'])){
			$set[] = "sale_money = '".$data['sale_money']."'";
		}

        if(!empty($data['pay_sale_money'])){//出库单摊销总价
			$set[] = "pay_sale_money = '".$data['pay_sale_money']."'";
		}

		if(!empty($data['status'])){
			$set[] = "status = '".$data['status']."'";
		}

		if(!empty($data['out_date'])){
			$set[] = "out_date = '".$data['out_date']."'";
		}

		if(!empty($data['date_added'])){
			$set[] = "date_added = '".$data['date_added']."'";
		}

		if(!empty($data['comment'])){
			$set[] = "comment = '".$data['comment']."'";
		}

		$sql .= ' SET '.implode(',',$set);

		if(!empty($out_id)){
			$sql .= " WHERE out_id = '".$out_id."'";
			$result = $this->db->query($sql);
			return $result;
		}else
			return false;
	}

	public function addetail($data,$warehouse_id,$mark){

		//减库存
		if($mark){
			$sql = "UPDATE `".DB_PREFIX."inventory` SET available_quantity = available_quantity - ".intval($data['product_quantity'])." WHERE product_code = '".$data['product_code']."' AND warehouse_id = '".$warehouse_id."'";

			$this->db->query($sql);

			$sql = "SELECT id FROM `".DB_PREFIX."inventory` WHERE warehouse_id = '".$warehouse_id."' AND product_code = '".$data['product_code']."'";

			$id_query = $this->db->query($sql);

			if (0 != (int)$data['product_quantity']){
			    $inventoryHistoryData = array(
			        'inventory_id'  => $id_query->row['id'],//inventory表PK
			        'product_code'  => $data['product_code'],//商品编码（冗余字段）
			        'warehouse_id'  => $warehouse_id,//仓库编号（冗余字段）
			        'account_qty'   => 0,//财务数量变更（正+ 负-）
			        'available_qty' => (int)(0 - $data['product_quantity']),//可用数量变更（正+ 负-）
			        'comment'       => '出库单：'.$data['out_id'].' 添加商品条目',//备注（入库单完成、损益单完成、出库单生成、出库单修改、出库单作废）
			        'user_id'       => $this->user->getId(),//操作人PK
			        'date_added'    => date('Y-m-d H:i:s'),//条目创建时间
			    );
			    M('inventory_history')->data($inventoryHistoryData)->add();
			}
		}

		$sql = "INSERT INTO `".DB_PREFIX."stock_out_detail`";

		if(!empty($data['out_id'])){
			$set[] = "out_id = '".$data['out_id']."'";
		}

		if(!empty($data['product_code'])){
			$set[] = "product_code = '".$data['product_code']."'";
		}

		if(!empty($data['product_quantity'])){
			$set[] = "product_quantity = '".$data['product_quantity']."'";
		}

		if(!empty($data['product_price'])){
			$set[] = "product_price = '".$data['product_price']."'";
		}

        if(!empty($data['pay_product_price'])){//摊销价
			$set[] = "pay_product_price = '".$data['pay_product_price']."'";
		}

		if(!empty($data['products_money'])){
			$set[] = "products_money = '".$data['products_money']."'";
		}

		if(!empty($data['pay_products_money'])){//摊销总价
			$set[] = "pay_products_money = '".$data['pay_products_money']."'";
		}

		if(!empty($data['date_added'])){
			$set[] = "date_added = '".$data['date_added']."'";
		}else{
			$set[] = "date_added = NOW()";
		}

		$sql .= ' SET '.implode(',',$set);

		$this->db->query($sql);

		$id = $this->db->getLastId();

		return $id;
	}

	public function updatedetail($data,$id,$new_warehouse_id,$old_warehouse_id,$mark){
		if($mark){
			$sql = "SELECT out_id FROM `".DB_PREFIX."stock_out_detail` WHERE id = '".$id."'";

			$out_id_query = $this->db->query($sql);

			if($new_warehouse_id == $old_warehouse_id){
				$sql = "UPDATE `".DB_PREFIX."inventory` SET available_quantity = available_quantity - ".intval($data['remaindnum'])." WHERE product_code = '".$data['product_code']."' AND warehouse_id = '".$new_warehouse_id."'";

				$this->db->query($sql);

				$sql = "SELECT id FROM `".DB_PREFIX."inventory` WHERE warehouse_id = '".$new_warehouse_id."' AND product_code = '".$data['product_code']."'";

				$id_query = $this->db->query($sql);

				if (0 != (int)$data['remaindnum']){
				    $inventoryHistoryData = array(
				        'inventory_id'  => $id_query->row['id'],//inventory表PK
				        'product_code'  => $data['product_code'],//商品编码（冗余字段）
				        'warehouse_id'  => $new_warehouse_id,//仓库编号（冗余字段）
				        'account_qty'   => 0,//财务数量变更（正+ 负-）
				        'available_qty' => (int)(0 - $data['remaindnum']),//可用数量变更（正+ 负-）
				        'comment'       => '出库单：'.$out_id_query->row['out_id'].' 修改商品条目',//备注（入库单完成、损益单完成、出库单生成、出库单修改、出库单作废）
				        'user_id'       => $this->user->getId(),//操作人PK
				        'date_added'    => date('Y-m-d H:i:s'),//条目创建时间
				    );
				    M('inventory_history')->data($inventoryHistoryData)->add();
				}
			}else{
				$sql = "SELECT product_quantity FROM `".DB_PREFIX."stock_out_detail` WHERE id = '".$id."'";

				$quantity_query = $this->db->query($sql);

				$quantity = $quantity_query->row['product_quantity'];

				$sql = "UPDATE `".DB_PREFIX."inventory` SET available_quantity = available_quantity + ".intval($quantity)." WHERE product_code = '".$data['product_code']."' AND warehouse_id = '".$old_warehouse_id."'";

				$this->db->query($sql);

				$sql = "SELECT id FROM `".DB_PREFIX."inventory` WHERE warehouse_id = '".$old_warehouse_id."' AND product_code = '".$data['product_code']."'";

				$id_query = $this->db->query($sql);

				if (0 != (int)$quantity){
				    $inventoryHistoryData = array(
				        'inventory_id'  => $id_query->row['id'],//inventory表PK
				        'product_code'  => $data['product_code'],//商品编码（冗余字段）
				        'warehouse_id'  => $old_warehouse_id,//仓库编号（冗余字段）
				        'account_qty'   => 0,//财务数量变更（正+ 负-）
				        'available_qty' => (int)$quantity,//可用数量变更（正+ 负-）
				        'comment'       => '出库单：'.$out_id_query->row['out_id'].' 修改商品条目',//备注（入库单完成、损益单完成、出库单生成、出库单修改、出库单作废）
				        'user_id'       => $this->user->getId(),//操作人PK
				        'date_added'    => date('Y-m-d H:i:s'),//条目创建时间
				    );
				    M('inventory_history')->data($inventoryHistoryData)->add();
				}

				$sql = "UPDATE `".DB_PREFIX."inventory` SET available_quantity = available_quantity - ".intval($data['product_quantity'])." WHERE product_code = '".$data['product_code']."' AND warehouse_id = '".$new_warehouse_id."'";

				$this->db->query($sql);

				$sql = "SELECT id FROM `".DB_PREFIX."inventory` WHERE warehouse_id = '".$new_warehouse_id."' AND product_code = '".$data['product_code']."'";

				$id_query = $this->db->query($sql);

				if (0 != (int)$data['product_quantity']){
				    $inventoryHistoryData = array(
				        'inventory_id'  => $id_query->row['id'],//inventory表PK
				        'product_code'  => $data['product_code'],//商品编码（冗余字段）
				        'warehouse_id'  => $new_warehouse_id,//仓库编号（冗余字段）
				        'account_qty'   => 0,//财务数量变更（正+ 负-）
				        'available_qty' => (int)(0 - $data['product_quantity']),//可用数量变更（正+ 负-）
				        'comment'       => '出库单：'.$data['out_id'].' 修改商品条目',//备注（入库单完成、损益单完成、出库单生成、出库单修改、出库单作废）
				        'user_id'       => $this->user->getId(),//操作人PK
				        'date_added'    => date('Y-m-d H:i:s'),//条目创建时间
				    );
				    M('inventory_history')->data($inventoryHistoryData)->add();
				}
			}
		}

		$sql = "UPDATE `".DB_PREFIX."stock_out_detail`";

		if(!empty($data['out_id'])){
			$set[] = "out_id = '".$data['out_id']."'";
		}

		if(!empty($data['product_code'])){
			$set[] = "product_code = '".$data['product_code']."'";
		}

		if(!empty($data['product_quantity'])){
			$set[] = "product_quantity = '".$data['product_quantity']."'";
		}

		if(!empty($data['product_price'])){
			$set[] = "product_price = '".$data['product_price']."'";
		}

        if(!empty($data['pay_product_price'])){
			$set[] = "pay_product_price = '".$data['pay_product_price']."'";
		}

		if(!empty($data['products_money'])){
			$set[] = "products_money = '".$data['products_money']."'";
		}

        if(!empty($data['pay_products_money'])){
			$set[] = "pay_products_money = '".$data['pay_products_money']."'";
		}

		if(!empty($data['data_added'])){
			$set[] = "data_added = '".$data['data_added']."'";
		}

		$sql .= ' SET '.implode(',',$set);

		if(!empty($id)){
			$sql .= " WHERE id = '".$id."'";
			$result = $this->db->query($sql);
			return $result;
		}else
			return false;
	}

	//导入相关
	public function import(){

		set_time_limit(0);

		// $query_count = $this->db->query("SELECT COUNT(order_id) AS count FROM `". DB_PREFIX ."order` WHERE order_status_id = 17 || order_status_id = 18 || order_status_id = 5 || order_status_id = 3");
		// $count = $query_count->row['count'];

		// echo $count;exit;
		$singletime = 50;

		// $totaltimes = $this->totalpage($count,$singletime);

		$wuxiid_arr = $this->getwuxiid();

		for($i = 0;$i <= 0;$i++){

			$sql = "SELECT order_id,total,out_time FROM `". DB_PREFIX ."order` WHERE order_status_id = 17 || order_status_id = 18 || order_status_id = 5 || order_status_id = 3 ORDER BY order_id DESC LIMIT ".$i*$singletime . "," .($i+1)*$singletime;

			// echo $sql ;exit;

			$query_order = $this->db->query($sql);

			// print_r($query_order->rows);exit;

			foreach($query_order->rows as $val){
				$sql = "SELECT op.product_id,op.total,p.product_code,op.quantity,op.price FROM `".DB_PREFIX."order_product` op LEFT JOIN `".DB_PREFIX."product` p ON op.product_id = p.product_id WHERE op.order_id = '".$val['order_id']."'";

				$query_product = $this->db->query($sql);

				$query_product_rows = $query_product->rows;

				$total_arr = array();

				foreach($query_product_rows as $val2){
					if(in_array($val2['product_id'],$wuxiid_arr)){
						$total_arr[1]['sale_money'] += $val2['total'];
					}else{
						$total_arr[0]['sale_money'] += $val2['total'];
					}
				}

				if(count($total_arr)==2){

					$insert_data = array(
						'refer_id'     => $val['order_id'],
						'refer_type_id'   => 1,
						'warehouse_id' => 4,
						'sale_money'   => $total_arr[1]['sale_money'],
						'status'       => 2,
						'out_date'     => $val['out_time']
					);
					$out_id2 = $this->add($insert_data);
				}

				$insert_data = array(
					'refer_id'     => $val['order_id'],
					'refer_type_id'   => 1,
					'warehouse_id' => 10,
					'sale_money'   => $total_arr[0]['sale_money'],
					'status'       => 2,
					'out_date'     => $val['out_time']
				);
				$out_id = $this->add($insert_data);

				foreach($query_product_rows as $val2){
					if(in_array($val2['product_id'],$wuxiid_arr)){

						$insert_data = array(
							'out_id' => $out_id2,
							'product_code'     => $val2['product_code'],
							'product_quantity' => $val2['quantity'],
							'product_price'    => $val2['price'],
							'products_money'   => $val2['total'],
						);

						$this->addetail($insert_data);

					}else{

						$insert_data = array(
							'out_id' => $out_id,
							'product_code'     => $val2['product_code'],
							'product_quantity' => $val2['quantity'],
							'product_price'    => $val2['price'],
							'products_money'   => $val2['total'],
						);

						$this->addetail($insert_data);

					}
				}
				
			}

		}
		echo "导入完成";
	}

	public function getstockouts($data = array()){
		$sql = "SELECT so.*,rt.name AS rtname,wh.name AS whname,u.username,ts.name AS tsname,o.shipping_country,o.shipping_zone,o.shipping_city,o.shipping_address,o.payment_method,o.is_pay,o.logcenter_id,o.date_added AS order_date_added,o.auditted_time AS order_auditted_time FROM `".DB_PREFIX."stock_out` so LEFT JOIN `".DB_PREFIX."refer_type` rt ON so.refer_type_id = rt.refer_type_id LEFT JOIN `".DB_PREFIX."warehouse` wh ON so.warehouse_id = wh.warehouse_id LEFT JOIN `".DB_PREFIX."user` u ON so.user_id = u.user_id LEFT JOIN `".DB_PREFIX."inout_state` ts ON so.status = ts.id LEFT JOIN `".DB_PREFIX."order` o ON so.refer_id = o.order_id";

		if(!empty($data['filter_refer_id'])){
			$where[] = "so.refer_id = '".$data['filter_refer_id']."'";
		}
		if(!empty($data['out_id'])){
			$where[] = "so.out_id = '".$data['out_id']."'";
		}

		if(!empty($data['filter_name_code'])){

			$sql_name_code = "SELECT pov.product_code FROM `".DB_PREFIX."product_description` pd LEFT JOIN `".DB_PREFIX."product_option_value` pov ON pov.product_id = pd.product_id WHERE pd.name like '%".$data['filter_name_code']."%'";

			$query_code = $this->db->query($sql_name_code);

			foreach($query_code->rows as $val){
				if(!empty($val['product_code'])){
					$array_code[] = $val['product_code'];
				}
			}

			$string_code = implode(',',$array_code);

			// echo '<pre>';
			// print_r($array_code);
			// echo '</pre>';
			// exit;
			
			$sql_name_code = "SELECT out_id FROM `".DB_PREFIX."stock_out_detail` WHERE product_code LIKE '%".$data['filter_name_code']."%'";

			if(!empty($array_code)){
				$sql_name_code .= ' OR product_code IN ('.$string_code.")";
			}

			$query_out_id = $this->db->query($sql_name_code);

			foreach($query_out_id->rows as $val){
				$array_out_id[] = $val['out_id'];
			}

			$where[] = "so.out_id IN (".implode(',',$array_out_id).")";
			
		}

		if(!empty($data['filter_user_name'])){
			$where[] = "u.username = '".$data['filter_user_name']."'";
		}

		if(!empty($data['filter_warehouse'])){
			$where[] = "wh.warehouse_id = '".$data['filter_warehouse']."'";
		}

		if(!empty($data['filter_add_date_start'])){
			$where[] = "so.date_added >= '".$data['filter_add_date_start']."'";
		}

		if(!empty($data['filter_add_date_end'])){
			$where[] = "so.date_added <= '".$data['filter_add_date_end']."'";
		}

		if(!is_null($data['filter_status'])){
			$where[] = "so.status = '".$data['filter_status']."'";
		}else{
			$where[] = "so.status != 0";
		}

		if(!empty($where))
			$sql .= ' WHERE '.implode(' AND ',$where);

		$sort_data = array(
			'so.out_id',
			'so.refer_id',
			'so.refer_type_id',
			'so.warehouse_id',
			'so.user_id',
			'so.sale_money',
			'so.status',
			'so.out_date',
			'so.date_added',
			'o.logcenter_id'
		);

		// if (isset($data['sort']) && in_array($data['sort'], $sort_data)) {
		// 	$sql .= " ORDER BY " . $data['sort'];
		// } else {
			$sql .= " ORDER BY so.date_added";
		// }

		// if (isset($data['order']) && ($data['order'] == 'DESC')) {
			$sql .= " DESC";
		// } else {
		// 	$sql .= " ASC";
		// }

		if (isset($data['start']) || isset($data['limit'])) {
			if ($data['start'] < 0) {
				$data['start'] = 0;
			}

			if ($data['limit'] < 1) {
				$data['limit'] = 20;
			}

			$sql .= " LIMIT " . (int)$data['start'] . "," . (int)$data['limit'];
		}

		$query = $this->db->query($sql);

		$result = $query->rows;

		foreach($result as $key=>$val){
            if (4 == $val['refer_type_id']) {
                $sql = "SELECT name FROM `".DB_PREFIX."warehouse` WHERE warehouse_id =(SELECT `in_warehouse_id` FROM `".DB_PREFIX."requisition` WHERE `id`='".$val['refer_id']."')";
            }else{
                $sql = "SELECT name FROM `".DB_PREFIX."warehouse` WHERE warehouse_id = '".$val['logcenter_id']."'";
            }
            $ware_query = $this->db->query($sql);
            $result[$key]['send_ware_name'] = $ware_query->row['name'];
		}

		return $result;
	}

	public function gettotalstockout($data = array()){
		$sql = "SELECT COUNT(out_id) AS count FROM `".DB_PREFIX."stock_out` so LEFT JOIN `".DB_PREFIX."user` u ON so.user_id = u.user_id LEFT JOIN `".DB_PREFIX."warehouse` wh ON so.warehouse_id = wh.warehouse_id";

		if(!empty($data['filter_refer_id'])){
			$where[] = "so.refer_id = '".$data['filter_refer_id']."'";
		}

		if(!empty($data['filter_name_code'])){

			$sql_name_code = "SELECT pov.product_code FROM `".DB_PREFIX."product_description` pd LEFT JOIN `".DB_PREFIX."product_option_value` pov ON pov.product_id = pd.product_id WHERE pd.name like '%".$data['filter_name_code']."%'";

			$query_code = $this->db->query($sql_name_code);

			foreach($query_code->rows as $val){
				if(!empty($val['product_code'])){
					$array_code[] = $val['product_code'];
				}
			}

			$string_code = implode(',',$array_code);

			// echo '<pre>';
			// print_r($array_code);
			// echo '</pre>';
			// exit;
			
			$sql_name_code = "SELECT out_id FROM `".DB_PREFIX."stock_out_detail` WHERE product_code LIKE '%".$data['filter_name_code']."%'";

			if(!empty($array_code)){
				$sql_name_code .= ' OR product_code IN ('.$string_code.")";
			}

			$query_out_id = $this->db->query($sql_name_code);

			foreach($query_out_id->rows as $val){
				$array_out_id[] = $val['out_id'];
			}

			$where[] = "so.out_id IN (".implode(',',$array_out_id).")";
			
		}

		if(!empty($data['filter_user_name'])){
			$where[] = "u.username = '".$data['filter_user_name']."'";
		}

		if(!empty($data['filter_warehouse'])){
			$where[] = "wh.warehouse_id = '".$data['filter_warehouse']."'";
		}

		if(!empty($data['filter_add_date_start'])){
			$where[] = "so.date_added >= '".$data['filter_add_date_start']."'";
		}

		if(!empty($data['filter_add_date_end'])){
			$where[] = "so.date_added <= '".$data['filter_add_date_end']."'";
		}

		if(!is_null($data['filter_status'])){
			$where[] = "so.status = '".$data['filter_status']."'";
		}else{
			$where[] = "so.status != 0";
		}

		if(!empty($where))
			$sql .= ' WHERE '.implode(' AND ',$where);

		$query_count = $this->db->query($sql);

		return $query_count->row['count'];
	}

	public function totalpage($count,$singletime){
		$totaltimes = $count%$singletime?intval($count/$singletime)+1:$count/$singletime;
		return $totaltimes;
	}

	public function getwuxiid(){
		$sql = "SELECT product_id FROM `".DB_PREFIX."product_description` where name LIKE '%妙洁%' OR name LIKE '%裕行%' OR name LIKE '%德勤%' OR name LIKE '%靓影%' OR name LIKE '%前力%' OR name LIKE '%卓净%' OR name LIKE '%红樱桃 大肚壶 1.3L%'";

		$query = $this->db->query($sql);

		foreach($query->rows as $val){
			$id_arr[] = $val['product_id'];
		}

		return $id_arr;
	}

	public function getstockdetail($out_id,$data = array()){
		if(!empty($out_id)){
			$sql = "SELECT sod.*,pd.name,p2.sku FROM `".DB_PREFIX."stock_out_detail` sod LEFT JOIN `".DB_PREFIX."product_option_value` pov ON sod.product_code = pov.product_code LEFT JOIN `".DB_PREFIX."product` p ON sod.product_code = p.product_code LEFT JOIN `".DB_PREFIX."product_description` pd ON IF(pov.product_id,pov.product_id,p.product_id) = pd.product_id LEFT JOIN `".DB_PREFIX."product` p2 ON IF(pov.product_id,pov.product_id,p.product_id) = p2.product_id WHERE sod.out_id = '".(int)$out_id."' AND (pov.product_code != '' OR p.product_code != '')";

			$sort_data = array(
				'id',
				'out_id',
				'pd.name',
				'p2.sku',
				'product_code',
				'product_quantity',
				'product_price',
				'products_money',
                'order_ids',
			);

			if (isset($data['sort']) && in_array($data['sort'], $sort_data)) {
				$sql .= " ORDER BY " . $data['sort'];
			} else {
				$sql .= " ORDER BY id";
			}

			if (isset($data['order']) && ($data['order'] == 'DESC')) {
				$sql .= " DESC";
			} else {
				$sql .= " ASC";
			}

			if (isset($data['start']) || isset($data['limit'])) {
				if ($data['start'] < 0) {
					$data['start'] = 0;
				}

				if ($data['limit'] < 1) {
					$data['limit'] = 20;
				}

				$sql .= " LIMIT " . (int)$data['start'] . "," . (int)$data['limit'];
			}
//echo $sql;
			$query = $this->db->query($sql);

			return $query->rows;
		}else
			return null;
	}

	public function gettotalstockoutdetail($out_id){
		$sql = "SELECT COUNT(id) AS total FROM `".DB_PREFIX."stock_out_detail`";

		if(isset($out_id)){
			$where[] = "out_id = '".$out_id."'";

			$sql .= ' WHERE '.implode(',',$where);

			$query = $this->db->query($sql);

			return $query->row['total'];
		}else{
			return null;
		}
	}

	public function getstockoutbyoutid($out_id){
		$sql = "SELECT so.*,rt.name AS rtname,wh.name AS whname,u.username,ts.name AS tsname,o.shipping_fullname,o.shipping_company,o.shipping_address,o.shipping_city,o.shipping_country,o.shipping_zone,o.logcenter_id FROM `".DB_PREFIX."stock_out` so LEFT JOIN `".DB_PREFIX."refer_type` rt ON so.refer_type_id = rt.refer_type_id LEFT JOIN `".DB_PREFIX."warehouse` wh ON so.warehouse_id = wh.warehouse_id LEFT JOIN `".DB_PREFIX."user` u ON so.user_id = u.user_id LEFT JOIN `".DB_PREFIX."inout_state` ts ON so.status = ts.id LEFT JOIN `".DB_PREFIX."order` o ON so.refer_id = o.order_id";

		if($out_id){
			$sql .= ' WHERE out_id = '.$out_id;

			$query = $this->db->query($sql);

			$result = $query->row;

            if (4 == $result['refer_type_id']) {
                $sql = "SELECT name,address,manager,contact_tel FROM `".DB_PREFIX."warehouse` WHERE warehouse_id =(SELECT `in_warehouse_id` FROM `".DB_PREFIX."requisition` WHERE `id`='".$result['refer_id']."')";
            }else{
                $sql = "SELECT name,address,manager,contact_tel FROM `".DB_PREFIX."warehouse` WHERE warehouse_id = '".$result['logcenter_id']."'";
            }
            $ware_query = $this->db->query($sql);
            $result['send_ware_name'] = $ware_query->row['name'];
            $result['send_ware_address'] = $ware_query->row['address'];
            $result['send_ware_manager'] = $ware_query->row['manager'];
            $result['send_ware_contact_tel'] = $ware_query->row['contact_tel'];
            return $result;

		}else
			return null;
	}

    public function searchProducts($searchpara){
        //$sql = "SELECT pd.name as pdname, IF(pov.product_code != '',pov.product_code,p.product_code) as code, IF((SELECT price FROM `".DB_PREFIX."product_special` ps WHERE ps.product_id = p.product_id AND ((ps.date_start <= CURRENT_DATE() AND ps.date_end >= CURRENT_DATE()) OR (ps.date_start = '0000-00-00' AND ps.date_end = '0000-00-00')) GROUP BY ps.priority HAVING ps.priority = MIN(ps.priority)),(SELECT price FROM `".DB_PREFIX."product_special` ps WHERE ps.product_id = p.product_id AND ((ps.date_start <= CURRENT_DATE() AND ps.date_end >= CURRENT_DATE()) OR (ps.date_start = '0000-00-00' AND ps.date_end = '0000-00-00')) GROUP BY ps.priority HAVING ps.priority = MIN(ps.priority)),p.price) AS price, p.sku, od.name as odname, ovd.name as ovdname FROM `".DB_PREFIX."product_option_value` pov LEFT JOIN `".DB_PREFIX."product` p ON pov.product_id = p.product_id LEFT JOIN `".DB_PREFIX."product_description` pd ON pov.product_id = pd.product_id LEFT JOIN `".DB_PREFIX."option_description` od ON pov.option_id = od.option_id LEFT JOIN `".DB_PREFIX."option_value_description` ovd ON pov.option_value_id = ovd.option_value_id where (pov.product_code LIKE '%".$searchpara."%' OR pd.name LIKE '%".$searchpara."%' OR p.product_code LIKE '%".$searchpara."%') AND (pov.product_code != '' OR p.product_code != '') LIMIT 0,5";
        $ret = array();
        $sql = "
        SELECT
            POV.`product_code`,POV.`product_id`,
            P.`sku`,P.`sale_price`,
            PD.`name` AS p_name,
            OD.`name` AS po_name,
            OVD.`name` AS pov_name
        FROM
            `product_option_value` AS POV
            LEFT JOIN `product` AS P ON (POV.`product_id`=P.`product_id`)
            LEFT JOIN `product_description` AS PD ON (POV.`product_id`=PD.`product_id` AND PD.`language_id`='".(int)$this->config->get('config_language_id')."')
            LEFT JOIN `option_description` AS OD ON (POV.`option_id`=OD.`option_id` AND OD.`language_id`='".(int)$this->config->get('config_language_id')."')
            LEFT JOIN `option_value_description` AS OVD ON (POV.`option_value_id`=OVD.`option_value_id` AND OVD.`language_id`='".(int)$this->config->get('config_language_id')."')
        WHERE
            POV.`product_code` LIKE '%".$searchpara."%' OR PD.`name` LIKE '%".$searchpara."%'
        ORDER BY POV.`product_id` DESC
        LIMIT 0,5
        ";
        $query = $this->db->query($sql);
        foreach ($query->rows as $row) {
            $today = date('Ymd');
            $s = "SELECT `price` FROM `product_special` WHERE `product_id`='".$row['product_id']."' AND `customer_group_id`='".(int)$this->config->get('config_customer_group_id')."' AND ((`date_start`='0' AND `date_end`='0') OR (`date_start`<='".$today."' AND `date_end`>='".$today."') OR (`date_start`<='".$today."' AND `date_end`='0') OR (`date_start`='0' AND `date_end`>='".$today."')) ORDER BY `product_special_id` DESC";
            $q = $this->db->query($s);
            $price = floatval($q->row['price']);
            if ($price < 0.01) {
                $price = $row['sale_price'];
            }
            $ret[] = array(
                'pdname' => $row['p_name'],
                'code' => $row['product_code'],
                'price' => $price,
                'sku' => $row['sku'],
                'odname' => $row['po_name'],
                'ovdname' => $row['pov_name'],
            );
        }
        return $ret;
    }

	public function deletedetail($id,$warehouse_id,$mark){
		if(!empty($id)){
			$sql = "SELECT product_code,product_quantity,out_id FROM `".DB_PREFIX."stock_out_detail` WHERE id = '".$id."'";

			$query_detail = $this->db->query($sql);

			if($mark){

				$sql = "UPDATE `".DB_PREFIX."inventory` SET available_quantity = available_quantity + ".intval($query_detail->row['product_quantity'])." WHERE product_code = '".$query_detail->row['product_code']."' AND warehouse_id = '".$warehouse_id."'";

				$this->db->query($sql);

				//添加库存变动记录

				$sql = "SELECT id FROM `".DB_PREFIX."inventory` WHERE warehouse_id = '".$warehouse_id."' AND product_code = '".$query_detail->row['product_code']."'";

				$id_query = $this->db->query($sql);

				if (0 != (int)$query_detail->row['product_quantity']){
				    $inventoryHistoryData = array(
				        'inventory_id'  => $id_query->row['id'],//inventory表PK
				        'product_code'  => $query_detail->row['product_code'],//商品编码（冗余字段）
				        'warehouse_id'  => $warehouse_id,//仓库编号（冗余字段）
				        'account_qty'   => 0,//财务数量变更（正+ 负-）
				        'available_qty' => (int)$query_detail->row['product_quantity'],//可用数量变更（正+ 负-）
				        'comment'       => '出库单：'.$query_detail->row['out_id'].' 删除商品条目',//备注（入库单完成、损益单完成、出库单生成、出库单修改、出库单作废）
				        'user_id'       => $this->user->getId(),//操作人PK
				        'date_added'    => date('Y-m-d H:i:s'),//条目创建时间
				    );
				    M('inventory_history')->data($inventoryHistoryData)->add();
				}

			}

			$sql = "DELETE FROM `".DB_PREFIX."stock_out_detail` WHERE id = '".$id."'";

			$result = $this->db->query($sql);

			return $result;
		}else
			return false;
	}

	public function getrefertype(){
		$sql = "SELECT * FROM `".DB_PREFIX."refer_type`";

		$query = $this->db->query($sql);

		return $query->rows;
	}

	public function getWarehouse($active){
		if($active){
			$sql = "SELECT * FROM `".DB_PREFIX."warehouse` WHERE status = 1";
		}else{
			$sql = "SELECT * FROM `".DB_PREFIX."warehouse`";
		}

		$query = $this->db->query($sql);

		return $query->rows;
	}

	public function getStatus(){
		$sql = "SELECT * FROM `".DB_PREFIX."inout_state`";

		$query = $this->db->query($sql);

		return $query->rows;
	}

	public function addstockhistory($data){
		$sql = "INSERT INTO `".DB_PREFIX."stock_out_history`";

		if(!empty($data['out_id'])){
			$set[] = "out_id = '".$data['out_id']."'";
		}

		if(!empty($data['user_id'])){
			$set[] = "user_id = '".$data['user_id']."'";

			$usersql = "SELECT username FROM `".DB_PREFIX."user` WHERE user_id = '".$data['user_id']."'";

			$query = $this->db->query($usersql);

			$username = $query->row['username'];

			$set[] = "operator_name = '".$username."'";
		}

		if(!empty($data['notify'])){
			$set[] = "notify = '".$data['notify']."'";
		}else{
			$set[] = "notify = 0";
		}

		if(!empty($data['comment'])){
			$set[] = "comment = '".$data['comment']."'";
		}

		if(!empty($data['date_added'])){
			$set[] = "date_added = '".$data['date_added']."'";
		}else{
			$set[] = "date_added = NOW()";
		}

		$sql .= ' SET '.implode(',',$set);

		$this->db->query($sql);

		$id = $this->db->getLastId();

		return $id;
	}

	public function getstockhistory($out_id){
		if(!empty($out_id)){
			$sql = "SELECT * FROM stock_out_history WHERE out_id = '".$out_id."' ORDER BY date_added DESC";

			$query = $this->db->query($sql);

			return $query->rows;
		}else
			return false;
	}

	public function getexportstock($out_id){
		if(intval($out_id)){
			$sql = "SELECT so.sale_money,so.warehouse_id,so.refer_type_id,so.refer_id,so.comment,o.date_added,o.shipping_company,o.shipping_address,o.shipping_zone,o.shipping_city,o.shipping_country,o.payment_method,o.is_pay,o.customer_id,o.fullname,o.payment_fullname,o.telephone FROM `".DB_PREFIX."stock_out` so LEFT JOIN `".DB_PREFIX."order` o ON o.order_id = so.refer_id";

			$sql .= ' WHERE out_id = '.$out_id;

            $query = $this->db->query($sql);

            $order_result = $query->row;
            
            if (4 == $order_result['refer_type_id']) {
                $sql = "SELECT name,address,manager,contact_tel FROM `".DB_PREFIX."warehouse` WHERE warehouse_id =(SELECT `in_warehouse_id` FROM `".DB_PREFIX."requisition` WHERE `id`='".$order_result['refer_id']."')";
            }else{
                $sql = "SELECT name,address,manager,contact_tel FROM `".DB_PREFIX."warehouse` WHERE warehouse_id = '".$order_result['logcenter_id']."'";
            }
            $ware_query = $this->db->query($sql);
            $order_result['name'] = $ware_query->row['name'];
            $order_result['address'] = $ware_query->row['address'];
            $order_result['manager'] = $ware_query->row['manager'];
            $order_result['contact_tel'] = $ware_query->row['contact_tel'];

			//$sql = "SELECT sod.*,pd.name, IF(pov.product_id,pov.product_id,p.product_id) AS product_id, i.position1, i.position2 FROM `".DB_PREFIX."stock_out_detail` sod LEFT JOIN `".DB_PREFIX."product_option_value` pov ON sod.product_code = pov.product_code LEFT JOIN `".DB_PREFIX."product` p ON sod.product_code = p.product_code LEFT JOIN `".DB_PREFIX."product_description` pd ON IF(pov.product_id,pov.product_id,p.product_id) = pd.product_id LEFT JOIN `".DB_PREFIX."inventory` i ON i.product_code = sod.product_code WHERE sod.out_id = '".(int)$out_id."' AND (pov.product_code != '' OR p.product_code != '') AND i.warehouse_id = '".$order_result['warehouse_id']."'";
            //出库单商品排序后打印
            $sql = "
            SELECT
                SOD.*,
                PD.`name`,
                POV.`product_id`,
                I.`position1`,
                I.`position2`
            FROM
                `".DB_PREFIX."stock_out_detail` AS SOD
                LEFT JOIN `".DB_PREFIX."product_option_value` AS POV ON (SOD.`product_code`=POV.`product_code`)
                LEFT JOIN `".DB_PREFIX."product_description` AS PD ON (POV.`product_id`=PD.`product_id`)
                LEFT JOIN `".DB_PREFIX."inventory` AS I ON (I.`product_code`=SOD.`product_code`)
            WHERE
                SOD.`out_id`='".(int)$out_id."' AND I.`warehouse_id`= '".$order_result['warehouse_id']."'
            ORDER BY SOD.`order_ids` ASC";

			$query = $this->db->query($sql);

			$detail_result = $query->rows;

			$product_data = array();

			foreach($detail_result as $val){

				$sql = "SELECT sku FROM `".DB_PREFIX."product` WHERE product_id = '".$val['product_id']."'";

				$query = $this->db->query($sql);

				$product_data[] = array(
					'product_name' => $val['name'],
					'sku' => $query->row['sku'],
					'position1' => $val['position1'],
					'position2' => $val['position2'],
					'num' => $val['product_quantity'],
					'price' => $val['product_price'],
					'total' => $val['products_money'],
					'product_code' => $val['product_code'],
                    'order_ids' => ($val['order_ids']%10000)?intval($val['order_ids']/10000).'-'.($val['order_ids']%10000):intval($val['order_ids']/10000),
				);
			}  

			$return_result = array(
				'shipping_company' => $order_result['shipping_company'],
				'shipping_address' => $order_result['shipping_address'],
				'shipping_country' => $order_result['shipping_country'],
				'shipping_zone'    => $order_result['shipping_zone'],
				'shipping_city'    => $order_result['shipping_city'],
				'refer_id' => $order_result['refer_id'],
				'date_added' => $order_result['date_added'],
				'customer_id' => $order_result['customer_id'],
				'fullname' => $order_result['fullname'],
				'payment_fullname' => $order_result['payment_fullname'],
				'telephone' => $order_result['telephone'],
				'payment_method' => $order_result['payment_method'],
				'is_pay' => $order_result['is_pay'],
				'comment' => $order_result['comment'],
				'name' => $order_result['name'],
				'manager' => $order_result['manager'],
				'contact_tel' => $order_result['contact_tel'],
				'address' => $order_result['address'],
				'product_data' => $product_data,
			);

			return $return_result;
		}
	}

	public function searchProduct($searchpara){
        $sql = "SELECT PD.`name`,POV.`product_id`,POV.`product_code` AS code,P.`sale_price` AS price,P.`sku`,OD.`name` AS odname,OVD.`name` AS ovdname FROM `".DB_PREFIX."product_option_value` AS POV LEFT JOIN `".DB_PREFIX."product` AS P ON POV.product_id=P.product_id LEFT JOIN `".DB_PREFIX."product_description` AS PD ON POV.product_id = PD.product_id LEFT JOIN `".DB_PREFIX."option_description` AS OD ON POV.option_id = OD.option_id LEFT JOIN `".DB_PREFIX."option_value_description` AS OVD ON POV.option_value_id = OVD.option_value_id WHERE POV.`product_code`='".$searchpara."' OR P.`product_code`='".$searchpara."'";
        $query = $this->db->query($sql);
        $ret = $query->row;
        if ($ret['product_id']) {
            $s = "SELECT `price` FROM `product_special` WHERE `product_id`='".$ret['product_id']."' AND `customer_group_id`='".(int)$this->config->get('config_customer_group_id')."' ORDER BY `priority` ASC,`price` ASC LIMIT 1";
            $q = $this->db->query($s);
            $t = $query->row;
            if ($t['price'])
                $ret['price'] = $t['price'];
        }
        return $ret;
/*
		$sql = "SELECT pd.name as name, IF(pov.product_code != '',pov.product_code,p.product_code) as code, IF((SELECT price FROM `".DB_PREFIX."product_special` ps WHERE ps.product_id = p.product_id AND ((ps.date_start <= CURRENT_DATE() AND ps.date_end >= CURRENT_DATE()) OR (ps.date_start = '0000-00-00' AND ps.date_end = '0000-00-00')) GROUP BY ps.priority HAVING ps.priority = MIN(ps.priority)),(SELECT price FROM `".DB_PREFIX."product_special` ps WHERE ps.product_id = p.product_id AND ((ps.date_start <= CURRENT_DATE() AND ps.date_end >= CURRENT_DATE()) OR (ps.date_start = '0000-00-00' AND ps.date_end = '0000-00-00')) GROUP BY ps.priority HAVING ps.priority = MIN(ps.priority)),p.sale_price) AS price, p.sku, od.name as odname, ovd.name as ovdname FROM `".DB_PREFIX."product_option_value` pov LEFT JOIN `".DB_PREFIX."product` p ON pov.product_id = p.product_id LEFT JOIN `".DB_PREFIX."product_description` pd ON pov.product_id = pd.product_id LEFT JOIN `".DB_PREFIX."option_description` od ON pov.option_id = od.option_id LEFT JOIN `".DB_PREFIX."option_value_description` ovd ON pov.option_value_id = ovd.option_value_id where (pov.product_code = '".$searchpara."' OR p.product_code = '".$searchpara."') AND (pov.product_code != '' OR p.product_code != '')";


		$query = $this->db->query($sql);
		
		return $query->row;
*/
    }

	public function getExportStockOuts($data = array()){
		$sql = "SELECT sod.*,o.order_id,rt.name AS rtname,wh.name AS whname,u.username,pd.name AS pdname,vs.vendor_name,p.sku,p.model,o.payment_method,o.is_pay,ts.name AS tsname,o.shipping_country,o.shipping_zone,o.shipping_city,o.shipping_address,o.auditted_time AS order_auditted_time,o.date_added AS order_date_added,o.logcenter_id,so.out_date,so.deliver_date,so.receive_date,so.comment FROM `".DB_PREFIX."stock_out_detail` sod LEFT JOIN `".DB_PREFIX."stock_out` so ON so.out_id = sod.out_id LEFT JOIN `".DB_PREFIX."order` o ON o.order_id = so.refer_id LEFT JOIN `".DB_PREFIX."refer_type` rt ON rt.refer_type_id = so.refer_type_id LEFT JOIN `".DB_PREFIX."warehouse` wh ON wh.warehouse_id = so.warehouse_id LEFT JOIN `".DB_PREFIX."user` u ON u.user_id = so.user_id LEFT JOIN `".DB_PREFIX."product_option_value` pov ON pov.product_code = sod.product_code LEFT JOIN `".DB_PREFIX."product` p ON p.product_id = pov.product_id LEFT JOIN `".DB_PREFIX."product_description` pd ON pd.product_id = pov.product_id LEFT JOIN `".DB_PREFIX."vendor` v ON v.vproduct_id = pov.product_id LEFT JOIN `".DB_PREFIX."vendors` vs ON vs.vendor_id = v.vendor LEFT JOIN `".DB_PREFIX."inout_state` ts ON ts.id = so.status";

		if(!empty($data['filter_refer_id'])){
			$where[] = "so.refer_id = '".$data['filter_refer_id']."'";
		}

		if(!empty($data['filter_name_code'])){
			$sql_name_code = "SELECT pov.product_code FROM `".DB_PREFIX."product_description` pd LEFT JOIN `".DB_PREFIX."product_option_value` pov ON pov.product_id = pd.product_id WHERE pd.name like '%".$data['filter_name_code']."%'";

			$query_code = $this->db->query($sql_name_code);

			foreach($query_code->rows as $val){
				if(!empty($val['product_code'])){
					$array_code[] = $val['product_code'];
				}
			}

			$string_code = implode(',',$array_code);

			// echo '<pre>';
			// print_r($array_code);
			// echo '</pre>';
			// exit;
			
			$sql_name_code = "SELECT out_id FROM `".DB_PREFIX."stock_out_detail` WHERE product_code LIKE '%".$data['filter_name_code']."%'";

			if(!empty($array_code)){
				$sql_name_code .= ' OR product_code IN ('.$string_code.")";
			}

			$query_out_id = $this->db->query($sql_name_code);

			foreach($query_out_id->rows as $val){
				$array_out_id[] = $val['out_id'];
			}

			$where[] = "so.out_id IN (".implode(',',$array_out_id).")";
		}

		if(!empty($data['filter_user_name'])){
			$where[] = "u.username = '".$data['filter_user_name']."'";
		}

		if(!empty($data['filter_warehouse'])){
			$where[] = "wh.warehouse_id = '".$data['filter_warehouse']."'";
		}

		if(!empty($data['filter_add_date_start'])){
			$where[] = "so.date_added >= '".$data['filter_add_date_start']."'";
		}

		if(!empty($data['filter_add_date_end'])){
			$where[] = "so.date_added <= '".$data['filter_add_date_end']."'";
		}

		if(!is_null($data['filter_status'])){
			$where[] = "so.status = '".$data['filter_status']."'";
		}else{
			$where[] = "so.status != 0";
		}

		if(!empty($where)){
			$sql .= ' WHERE '.implode(' AND ',$where);
		}

		$sort_data = array(
			'so.out_id',
			'so.refer_id',
			'so.refer_type_id',
			'so.warehouse_id',
			'so.user_id',
			'so.sale_money',
			'so.status',
			'so.out_date',
			'so.date_added',
			'o.shipping_zone_id'
		);

		if (isset($data['sort']) && in_array($data['sort'], $sort_data)) {
			$sql .= " ORDER BY " . $data['sort'];
		} else {
			$sql .= " ORDER BY so.out_id";
		}

		if (isset($data['order']) && ($data['order'] == 'DESC')) {
			$sql .= " DESC";
		} else {
			$sql .= " ASC";
		}

		$query = $this->db->query($sql);

		$result = $query->rows;

		foreach($result as $key=>$val){

			$sql = "SELECT name FROM `".DB_PREFIX."warehouse` WHERE warehouse_id = '".$val['logcenter_id']."'";

			$ware_query = $this->db->query($sql);

			$result[$key]['send_ware_name'] = $ware_query->row['name'];

		}

		return $result;
	}

	public function getAvailQuanByCodeAndWare($warehouse_id,$product_code){
		$sql = "SELECT available_quantity FROM `".DB_PREFIX."inventory` WHERE warehouse_id = '".$warehouse_id."' AND product_code = '".$product_code."'";

		$query_quantity = $this->db->query($sql);

		return $query_quantity->row['available_quantity'];
	}

	public function getStatusFromStockOut($out_id){
		$sql = "SELECT status FROM `".DB_PREFIX."stock_out` WHERE out_id = '".$out_id."'";

		$status_query = $this->db->query($sql);

		$status = $status_query->row['status'];

		return $status;
	}

	public function getWareFromStockOut($out_id){
		$sql = "SELECT warehouse_id FROM `".DB_PREFIX."stock_out` WHERE out_id = '".$out_id."'";

		$query_stock = $this->db->query($sql);

		return $query_stock->row['warehouse_id'];
	}

	public function getProductinfoFromStockOutDetail($out_id){
		$sql = "SELECT sod.id,sod.product_code,pd.name AS pdname,sod.counter_id,so.refer_id,sod.product_quantity FROM stock_out_detail sod LEFT JOIN `".DB_PREFIX."product_option_value` pov ON pov.product_code = sod.product_code LEFT JOIN `".DB_PREFIX."product_description` pd ON pd.product_id = pov.product_id LEFT JOIN `".DB_PREFIX."stock_out` so ON so.out_id = sod.out_id WHERE sod.out_id = '".$out_id."'";

		$stock_out_detail_query = $this->db->query($sql);

		return $stock_out_detail_query->rows;
	}

	public function getQuantityFromStockOutDetail($id){
		$sql = "SELECT product_quantity FROM `".DB_PREFIX."stock_out_detail` WHERE id = '".$id."'";

		$query_quantity = $this->db->query($sql);

		return $query_quantity->row['product_quantity'];
	}

	public function getWareinfoFromWarehouse($warehouse_id,$warehouse_id2){
		$sql = "SELECT warehouse_id,name FROM `".DB_PREFIX."warehouse` WHERE warehouse_id IN ('".$warehouse_id."','".$warehouse_id2."')";

		$warehouse_query = $this->db->query($sql);

		return $warehouse_query->rows;
	}

	public function getStockOutDetailProducts($out_id){

		$sql = "SELECT sod.product_code,sod.counter_id,so.refer_id FROM `".DB_PREFIX."stock_out_detail` sod LEFT JOIN `".DB_PREFIX."stock_out` so ON sod.out_id = so.out_id WHERE sod.out_id = '".$out_id."'";

		$products_query = $this->db->query($sql);

		return $products_query->rows;

	}

	public function getSumQuanFromStockOut($counter_id,$refer_id,$product_code){

		if(intval($counter_id)){

			$sql = "SELECT SUM(product_quantity) AS maxquan FROM `".DB_PREFIX."stock_out_detail` WHERE counter_id = '".intval($counter_id)."'";

		}else{

			$sql = "SELECT SUM(sod.product_quantity) AS maxquan FROM `".DB_PREFIX."stock_out_detail` sod LEFT JOIN `".DB_PREFIX."stock_out` so ON so.out_id = sod.out_id WHERE so.refer_id = '".$refer_id."' AND so.status != 0 AND sod.product_code = '".$product_code."'";

		}

		$sumquery = $this->db->query($sql);

		$maxquan = intval($sumquery->row['maxquan']);

		return $maxquan;

	}

	public function setOrderProductLackQuan($maxquan,$counter_id,$product_code,$refer_id){

		if(intval($counter_id)){

			$sql = "UPDATE `".DB_PREFIX."order_product_group` SET lack_quantity = IF((quantity - ".$maxquan.") < 0,0,(quantity - ".$maxquan.")) WHERE order_product_group_id = '".intval($val['counter_id'])."'";

		}else{

			$sql = "UPDATE `".DB_PREFIX."order_product` SET lack_quantity = IF((quantity - ".$maxquan.") < 0,0,(quantity - ".$maxquan.")) WHERE product_code = '".$product_code."' AND order_id = '".$refer_id."'";

		}

		return $this->db->query($sql);

	}

    //根据出库单编号，获取订单级优惠信息
    public function getOTList($outId) {
        $sql = "SELECT `title`,`value` FROM `".DB_PREFIX."order_total` WHERE `order_total_id` IN (SELECT `order_total_ids` FROM `stock_out` WHERE `out_id`='".$outId."')";
        $res = $this->db->query($sql);
        return $res->rows;
    }

    //根据出库单中订单级的优惠信息，更新出库单总价
    public function resetSOTotalWithOTList($outId) {
        $sql = "SELECT SUM(`value`) AS total FROM `".DB_PREFIX."order_total` WHERE `order_total_id` IN (SELECT `order_total_ids` FROM `stock_out` WHERE `out_id`='".$outId."')";
        $res = $this->db->query($sql);
        if ($res->row['total'] < 0) {
            $sql = "UPDATE `".DB_PREFIX."stock_out` SET `sale_money`=`sale_money`+".$res->row['total']." WHERE `out_id`='".$outId."'";
            $this->db->query($sql);
        }
    }

    //获取基于订单的最早最晚的各个时间截点
    public function getDateRangeGroupByOrderId() {
        $ret = array();
        $sql = "SELECT `refer_id`,GROUP_CONCAT(`date_added` SEPARATOR ';') AS date_added,GROUP_CONCAT(`out_date` SEPARATOR ';') AS out_date,GROUP_CONCAT(`deliver_date` SEPARATOR ';') AS deliver_date,GROUP_CONCAT(`receive_date` SEPARATOR ';') AS receive_date FROM `stock_out` WHERE `refer_type_id`=1 AND `status`>0 GROUP BY `refer_id`";
        $res = $this->db->query($sql);
        foreach ($res->rows as $row) {
            extract($this->_getFromGroupConcat($row['date_added'], 'DateAdded'));
            extract($this->_getFromGroupConcat($row['out_date'], 'OutDate'));
            extract($this->_getFromGroupConcat($row['deliver_date'], 'DeliverDate'));
            extract($this->_getFromGroupConcat($row['receive_date'], 'ReceiveDate'));
            $ret[$row['refer_id']] = compact('firstDateAdded', 'lastDateAdded', 'firstOutDate', 'lastOutDate', 'firstDeliverDate', 'lastDeliverDate', 'firstReceiveDate', 'lastReceiveDate');
        }
        return $ret;
    }

    protected function _getFromGroupConcat($string, $suffix) {
        $defaultValue = '0000-00-00 00:00:00';
        $arrayTemp = explode(';', $string);
        ${'first'.$suffix} = '';
        ${'last'.$suffix} = '';
        foreach ($arrayTemp as $t) {
            if ($defaultValue != $t) {
                if (${'first'.$suffix}) {
                    ${'first'.$suffix} = min($t, ${'first'.$suffix});
                }else{
                    ${'first'.$suffix} = $t;
                }
                if (${'last'.$suffix}) {
                    ${'last'.$suffix} = max($t, ${'first'.$suffix});
                }else{
                    ${'last'.$suffix} = $t;
                }
            }
        }
        return compact('first'.$suffix, 'last'.$suffix);
    }
}
